package win.zxshuo.www.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import win.zxshuo.www.model.AuthorModel;
import win.zxshuo.www.model.BookModel;

import javax.transaction.Transactional;
import java.util.List;
import java.util.Map;

@Repository
public interface BookRepository extends JpaRepository<BookModel,Long> {

    /**
     * 获取使用该chapter表的数量
     * @param num
     * @return
     */
    @Query(value = "select count(*) from book where chapterTable=?1",nativeQuery = true)
    public Integer getBookNumByChapterTable(Integer num);

    /**
     * 获取最后一个chapter表，用来定位最后一个chapter判断是否采用该章节表
     * @return
     */
    @Query(value = "select chapterTable from book group by chapterTable order by chapterTable desc limit 1",nativeQuery = true)
    public Integer getLatestChapterTable();


    @Query(value = "select chapterTable,count(*) from book group by chapterTable order by chapterTable desc limit 1",nativeQuery = true)
    public  List<Object[]> getLastChapterTableInUse();


    /**
     * 通过作者名称获得名下的所有书籍
     * @param name
     * @return
     */
    @Query(value = "select b.* from book b,author a where a.id =b.authorId and a.name = ?1",nativeQuery = true)
    public List<BookModel> getBookModelByAuthorName(String name);

    /**
     * 通过书籍名和作者名字来查找书籍
     * @param name
     * @param authorModel
     * @return
     */
    public BookModel findByNameAndAuthorModel(String name, AuthorModel authorModel);

    /**
     * 获取所有更新中的书籍
     * @return
     */
    @Query(value = "select * from book where status = 0",nativeQuery = true)
    public List<BookModel> getAllBooksThatAreUpdating();

    @Query(value = "select * from book where id in ?1",nativeQuery = true)
    public List<BookModel> getBooksByIds(List<Long> intIds);


    @Modifying
    @Transactional // 小伙伴们如果自己写的话，请加上事物，否则会报错
    @Query(value = "update book set wordNum = wordNum + :wordnum where id = :bookid ",nativeQuery = true)
    public void addWordnumForBook(@Param("bookid") Long bookid,@Param("wordnum") Integer wordnum);



    @Query(value = "SELECT * FROM book WHERE hour(timediff(now(),createDate))>6 limit 1000",nativeQuery = true)
    public List<BookModel> findAllForFirstOneThousand();

    /**
     * 查询所有需要更新的书籍（包括已经完本但未采集的，和未完本的。）
     * @return
     */
    @Query(value = "select b.* from book b left join bookcrawler bc on b.id=bc.bookid where (bc.id is null) or (bc.isFinished!=1 and hour(timediff(now(),lastUpdated))>6) or (bc.isFinished =1  and bc.finishedBookFinishedCrawler = 0 )",nativeQuery = true)
    public List<BookModel> getAllBooksNeedToUpdate();

    @Query(value = "select * from book where name like CONCAT('%', :bookname, '%')",nativeQuery = true)
    public List<BookModel> findBookModelLike(@Param("bookname") String bookname);

}
